Code for Import Excel To Notes

Notes/Domino 8 Forum

Subject:

Here here is the code to import data.
The only function u need to modify here is the ImportData Function

'*************************************************
'Declaration
' From Andre Guirard

Dim Filter As String
Dim FileName As String
Dim FileTitle As String
Dim TruncName As String

Dim VaultWIPRoot As String
Dim VaultWIPUserPath As String

Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As Long
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As Long
End Type

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (OPENFILENAME As tagOPENFILENAME) As Long

Dim OPENFILENAME As tagOPENFILENAME

Public Const OFN_ALLOWMULTISELECT = &H200
Public Const OFN_CREATEPROMPT = &H2000
Public Const OFN_ENABLEHOOK = &H20
Public Const OFN_ENABLETEMPLATE = &H40
Public Const OFN_ENABLETEMPLATEHANDLE = &H80
Public Const OFN_EXPLORER = &H80000
Public Const OFN_EXTENSIONDIFFERENT = &H400
Public Const OFN_FILEMUSTEXIST = &H1000
Public Const OFN_HIDEREADONLY = &H4
Public Const OFN_LONGNAMES = &H200000
Public Const OFN_NOCHANGEDIR = &H8
Public Const OFN_NODEREFERENCELINKS = &H100000
Public Const OFN_NOLONGNAMES = &H40000
Public Const OFN_NONETWORKBUTTON = &H20000
Public Const OFN_NOREADONLYRETURN = &H8000
Public Const OFN_NOTESTFILECREATE = &H10000
Public Const OFN_NOVALIDATE = &H100
Public Const OFN_OVERWRITEPROMPT = &H2
Public Const OFN_PATHMUSTEXIST = &H800
Public Const OFN_READONLY = &H1
Public Const OFN_SHAREAWARE = &H4000
Public Const OFN_SHAREFALLTHROUGH = 2
Public Const OFN_SHARENOWARN = 1
Public Const OFN_SHAREWARN = 0
Public Const OFN_SHOWHELP = &H10
'*************************************************
'Initialize

Sub Initialize
'This agent is used to import the data from Excel to Lotus Notes

'Create the COM object for the Excel file to import and hide it
Set varXLFile = CreateObject("Excel.Application")
varXLFile.Visible = False
Set varXLWorkbook = Nothing

'Prompt for the name of the file and try to open it

Call OpenCommDlg ("Select a File")
If FileName = "" Then
strXLFilename = "R:\Enterprise Networking Team\Domino Team\1. Projects\Active\APSCI\TerritoryCode.xls"
Else
strXLFilename = FileName
End If
varXLFile.Workbooks.Open strXLFilename

Set varXLWorkbook = varXLFile.ActiveWorkbook
Set varXLSheet = varXLWorkbook.ActiveSheet

'Create new document to save data for each row
Dim s As New NotesSession
Dim db As NotesDatabase
Set db = s.CurrentDatabase
Dim doc As NotesDocument

'Loop through all valid rows and call the function to read the values into lists
'Information to be imported from Excel
Call ImportData (db, varXLSheet, doc)

'Close the Excel objects
varXLWorkbook.Close False
varXLFile.Quit
Set varXLFile = Nothing
End Sub

'****************************************************************

Function ImportData (db As NotesDatabase, varXLSheet As Variant, doc As NotesDocument)

Dim ingRow As Double 'Row

lngRow = 2
While Not (varXLSheet.Cells(lngRow, 1).Value = "")
Set doc = db.CreateDocument()
doc.Form = "Your Form Name HERE" 'Form Name
Call doc.ReplaceItemValue( "itemName1", varXLSheet.Cells(lngRow, 1).Value) 'The Field Name - With Column A Value
Call doc.ReplaceItemValue( "itemName2", varXLSheet.Cells(lngRow, 2).Value) 'The Field Name - With Column B Value
'Add more here if you need.... in similar format
Call doc.ComputeWithForm(False, False)
Call doc.Save(True, False)
lngRow = lngRow +1
Wend

End Function

'****************************************************************
Function OpenCommDlg (strTitle)
Dim Title As String
Dim DefExt As String
Dim szCurDir As String
Dim APIResults%

'Give the dialog a caption title.
If strTitle = "" Then
Title = "Add supporting document" & Chr$(0)
Else
Title = strTitle
End If

'Allocate string space for returned strings
FileName = Chr$(0) & Space$(255) & Chr$(0)
FileTitle = Space$(255) & Chr$(0)

'If the user does not specify an extension, append TXT.
DefExt = "TXT" & Chr$(0)

'Set up the default directory
szCurDir = Curdir$ & Chr$(0)

'Set up the data structure before you call the GetOpenFileName
OPENFILENAME.lStructSize = Len(OPENFILENAME)

'If the OpenFile Dialog box is not linked to any form use this line.
'It will pass a null pointer.
OPENFILENAME.hwndOwner = 0&

OPENFILENAME.lpstrFilter = Filter
OPENFILENAME.nFilterIndex = 1
OPENFILENAME.lpstrFile = FileName
OPENFILENAME.nMaxFile = Len(FileName)
OPENFILENAME.lpstrFileTitle = FileTitle
OPENFILENAME.nMaxFileTitle = Len(FileTitle)
OPENFILENAME.lpstrTitle = Title
OPENFILENAME.Flags = OFN_FILEMUSTEXIST
OPENFILENAME.lpstrDefExt = DefExt
OPENFILENAME.hInstance = 0
OPENFILENAME.lpstrCustomFilter = 0
OPENFILENAME.nMaxCustFilter = 0
OPENFILENAME.lpstrInitialDir = szCurDir
OPENFILENAME.nFileOffset = 0
OPENFILENAME.nFileExtension = 0
OPENFILENAME.lCustData = 0
OPENFILENAME.lpfnHook = 0
OPENFILENAME.lpTemplateName = 0

'This will pass the desired data structure to the Windows API,
'which will in turn it uses to display the Open Dialog form.

APIResults% = GetOpenFileName(OPENFILENAME)

If APIResults% <> 0 Then
FileName = Cstr( OPENFILENAME.lpstrFile )
FileTitle = Cstr( OPENFILENAME.lpstrFileTitle )
OpenCommDlg = 1
Else
OpenCommDlg = 0
End If
End Function

Feedback response number WEBB7VFW3J created by ~Manny Asawemarlen on 08/31/2009